from dataclasses import dataclass
from typing import Optional
from flask import request, make_response, Blueprint
from sqlalchemy import Connection, text, Engine

BP_musician = Blueprint("BP_musician", __name__, url_prefix="/musician")
LOCAL_TEST = False
API_ROOT: str

engine: Engine
def make_engine(debug: bool):
    global API_ROOT
    if debug:
        API_ROOT = "http://localhost:38828/local_test"
    else:
        API_ROOT = "https://jw238.site/api"
    
    from sqlalchemy import create_engine
    engine_path = (
        "postgresql://Administrator@localhost/jwtest"
        if debug
        else "postgresql://jw238:123123@localhost/main"
    )
    print(f"[engine use] {engine_path}")
    engine: Engine = create_engine(engine_path)
    return engine

def init(debug: bool):
    global engine
    engine = make_engine(debug)

    with engine.begin() as conn:
        should = 0
        would = 0
        for tab in ["musics", "artists", "bili_uploader"]:
            should += 1
            would += _check_table(tab, conn)
        if should != would:
            # fatal error
            print("Fatal error: table not exists")
            return

    if not LOCAL_TEST:
        BP_musician.add_url_rule("/random", view_func=random, methods=["GET"])
        BP_musician.add_url_rule("/fromnid", view_func=fromnid, methods=["GET"])

def _check_table(table_name: str, conn: Connection):
    sql = f"""
    SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND tablename = '{table_name}';
    """
    result = conn.execute(text(sql))
    tables = result.fetchall()
    if len(tables) == 1:
        return 1
    return 0

def random():
    try:
        music = _random()
        return make_response(music, 200)
    except Exception as e:
        return make_response(str(e), 500)

@dataclass
class Music:
    id: int
    name: str
    bv: Optional[str]
    uuid: str
    artist_id: Optional[int]
    bili_uploader_id: Optional[int]

    def to_node_json(self):
        obj = {
            # "id": self.id,
            "name": self.name,
            "nid": self.uuid,
            "cover_url": f"{API_ROOT}/thumb/{self.uuid}.png",
            "audio_url": f"{API_ROOT}/audio/{self.uuid}.mp3",
        }
        if self.bv:
            obj["bv"] = self.bv
        if self.artist_id:
            obj["artist_id"] = self.artist_id
        if self.bili_uploader_id:
            obj["bili_uploader_id"] = self.bili_uploader_id
        return obj


def _random():
    with engine.begin() as conn:
        sql = f"""
        SELECT * FROM musics ORDER BY RANDOM() LIMIT 5;
        """
        result = conn.execute(text(sql))
        musics = [Music(*r) for r in result.fetchall()]
    
    links = [{
        "source": musics[0].uuid,
        "target": m.uuid,
    } for m in musics[1:]]
    nodes = [m.to_node_json() for m in musics]
    return {
        "nodes": nodes,
        "links": links,
    }


def fromnid():
    nid = request.args.get("nid")
    if not nid:
        return make_response("nid required", 400)
    try:
        music = _fromnid(nid)
        return make_response(music, 200)
    except Exception as e:
        return make_response(str(e), 500)
    
def _fromnid(nid: str):
    with engine.begin() as conn:
        sql = f"""
        SELECT * FROM musics WHERE uuid <> '{nid}' ORDER BY RANDOM() LIMIT 4 ;
        """
        result = conn.execute(text(sql))
        musics = [Music(*r) for r in result.fetchall()]
    links = [{
        "source": nid,
        "target": m.uuid,
    } for m in musics]
    nodes = [m.to_node_json() for m in musics]
    return {
        "nodes": nodes,
        "links": links,
    }

if __name__ == "__main__":
    LOCAL_TEST = True
    init(debug=True)
    print(_random())
